package work;

import work.config.Constant;
import work.fram.JDBCConnect;
import work.fram.KeyValueMapper;
import work.fram.SqlMaker;
import work.person.helper.ConditionHelper;
import work.utils.CommonExcelUtils;
import work.utils.TextUtils;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

public class 党员信息_处理所在支部及关联组织机构 {


    public static void main(String[] args) throws SQLException {
        Statement statement = JDBCConnect.getStatement();
        List<Map<String, String>> list = CommonExcelUtils.e2m(new File(Constant.work, "2019年党员信息 - 副本.xls"), "sheet0", 1);
        SqlMaker sqlMaker = new SqlMaker("tc_md_communist");
        KeyValueMapper keyValueMapper = sqlMaker.createKeyValueMapper();
        StringBuilder builder = new StringBuilder();

        for (Map<String, String> map : list) {
            keyValueMapper.reset();
            String name = map.get("姓名");
            String ic = map.get("身份证号码");

            ResultSet resultSet = statement.executeQuery("select sysid from tc_md_communist where name='" + name + "' and idcard='" + ic + "'");

            //只处理最后一个
            if (resultSet.last()) {
                String sysid = resultSet.getString("sysid");
                keyValueMapper.put("branch", map.get("人员所在支部"));
                if (sqlMaker.make(SqlMaker.TYPE_UPDATE, ConditionHelper.create("sysid", "=", sysid))) {
                    System.out.println(sqlMaker.getSql());
                    builder.append(sqlMaker.getSql()).append("\n");
                }
            }

        }

        TextUtils.textToClipboard(builder.toString());

    }

}
